﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using DTO;
namespace DAO
{
    public class PhanPhoiDAO
    {
        public static void InsertPhanPhoi(PhanPhoiDTO phanPhoi)
        {
            String strSql = "insert into PhanPhoi(DaiLy, LoaiGas, SoLuong, GiaGoc, GiaBan, ChietKhau, MaGiaoHang) values(?, ?, ?, ?, ?, ?, ?)";
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbTransaction tran = conn.BeginTransaction();
            try
            {   
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@DaiLy", OleDbType.VarWChar);
                com.Parameters.Add("@LoaiGas", OleDbType.VarWChar);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);
                com.Parameters.Add("@GiaGoc", OleDbType.Double);
                com.Parameters.Add("@GiaBan", OleDbType.Double);
                com.Parameters.Add("@ChietKhau", OleDbType.Integer);
                com.Parameters.Add("@MaGiaoHang", OleDbType.Integer);

                com.Parameters["@DaiLy"].Value = phanPhoi.DaiLy;
                com.Parameters["@LoaiGas"].Value = phanPhoi.LoaiGas;
                com.Parameters["@SoLuong"].Value = phanPhoi.SoLuong;
                com.Parameters["@GiaGoc"].Value = phanPhoi.GiaGoc;
                com.Parameters["@GiaBan"].Value = phanPhoi.GiaBan;
                com.Parameters["@ChietKhau"].Value = phanPhoi.ChietKhau;
                com.Parameters["@MaGiaoHang"].Value = phanPhoi.MaGiaoHang;
                com.ExecuteNonQuery();

                strSql = "Select @@IDENTITY as ID";
                com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                phanPhoi.Id = int.Parse(com.ExecuteScalar().ToString());
    
                tran.Commit();
                conn.Close();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                conn.Close();
                throw;
            }
        }
        public static void InsertPhanPhoi(OleDbTransaction tran, PhanPhoiDTO phanPhoi)
        {
            String strSql = "insert into PhanPhoi(DaiLy, LoaiGas, SoLuong, GiaGoc, GiaBan, ChietKhau, MaGiaoHang) values(?, ?, ?, ?, ?, ?, ?)";
            OleDbConnection conn = DataAccess.OpenOleConnection();
            try
            {
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@DaiLy", OleDbType.VarWChar);
                com.Parameters.Add("@LoaiGas", OleDbType.VarWChar);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);
                com.Parameters.Add("@GiaGoc", OleDbType.Double);
                com.Parameters.Add("@GiaBan", OleDbType.Double);
                com.Parameters.Add("@ChietKhau", OleDbType.Integer);
                com.Parameters.Add("@MaGiaoHang", OleDbType.Integer);

                com.Parameters["@DaiLy"].Value = phanPhoi.DaiLy;
                com.Parameters["@LoaiGas"].Value = phanPhoi.LoaiGas;
                com.Parameters["@SoLuong"].Value = phanPhoi.SoLuong;
                com.Parameters["@GiaGoc"].Value = phanPhoi.GiaGoc;
                com.Parameters["@GiaBan"].Value = phanPhoi.GiaBan;
                com.Parameters["@ChietKhau"].Value = phanPhoi.ChietKhau;
                com.Parameters["@MaGiaoHang"].Value = phanPhoi.MaGiaoHang;
                com.ExecuteNonQuery();

                strSql = "Select @@IDENTITY as ID";
                com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                phanPhoi.Id = int.Parse(com.ExecuteScalar().ToString());                
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        public static void UpdatePhanPhoi(PhanPhoiDTO phanphoi)
        {
            String strSql = "update PhanPhoi set DaiLy=?, LoaiGas=?, SoLuong=?, GiaGoc=?, GiaBan=?, MaGiaoHang=?, ChietKhau=? where ID=?";
            OleDbConnection conn = DataAccess.OpenOleConnection();
            OleDbTransaction tran = conn.BeginTransaction();
            try
            {                                
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@DaiLy", OleDbType.Integer);
                com.Parameters.Add("@LoaiGas", OleDbType.Integer);
                com.Parameters.Add("@SoLuong", OleDbType.Integer);
                com.Parameters.Add("@GiaGoc", OleDbType.Integer);
                com.Parameters.Add("@GiaBan", OleDbType.Integer);
                com.Parameters.Add("@MaGiaoHang", OleDbType.Integer);
                com.Parameters.Add("@ChietKhau", OleDbType.Integer);
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@DaiLy"].Value = phanphoi.DaiLy;
                com.Parameters["@LoaiGas"].Value = phanphoi.LoaiGas;
                com.Parameters["@SoLuong"].Value = phanphoi.SoLuong;
                com.Parameters["@GiaGoc"].Value = phanphoi.GiaGoc;
                com.Parameters["@GiaBan"].Value = phanphoi.GiaBan;
                com.Parameters["@MaGiaoHang"].Value = phanphoi.MaGiaoHang;
                com.Parameters["@ChietKhau"].Value = phanphoi.ChietKhau;
                com.Parameters["@ID"].Value = phanphoi.Id;
                com.ExecuteNonQuery();
                tran.Commit();
                conn.Close();
            }
            catch (Exception ex) {
                tran.Rollback();
                conn.Close();
                throw ex; 
            }
        }
        public static void DeletePhanPhoi(int id)
        {
            try
            {
                
                String connStr = DataAccess.GetConnString();
                OleDbConnection conn = DataAccess.OpenOleConnection();
                OleDbTransaction tran = conn.BeginTransaction();                
                //String strSQl =
                String str = "select * from PhanPhoi where ID = " + id;
                DataTable dt = DataAccess.Query(tran, str);
                int loaiGas = (int)dt.Rows[0]["LoaiGas"];
                int soLuong = (int)dt.Rows[0]["soLuong"];
                BinhGasDAO.TangSoLuong(loaiGas, soLuong);
                PhanPhoiDAO.DeletePhanPhoi(tran, id);
                tran.Commit();
                conn.Close();
            }
            catch (Exception ex) { }
        }
        public static void DeletePhanPhoi(OleDbTransaction tran, int id)
        {
            try
            {
                String strSql = "delete from PhanPhoi where ID=?";
                OleDbConnection conn = DataAccess.OpenOleConnection();                
                OleDbCommand com = new OleDbCommand(strSql, conn);
                com.Transaction = tran;
                com.Parameters.Add("@ID", OleDbType.Integer);

                com.Parameters["@ID"].Value = id;
                com.ExecuteNonQuery();
            }
            catch (Exception ex) { throw; }
        }
        public static void UpdateGiaPhanPhoi(float giaBan, float giaGoc, int id)
        {
            String strSql = "update PhanPhoi set GiaGoc=?, GiaBan=? where LoaiGas=?";
            String connStr = DataAccess.GetConnString();
            OleDbConnection conn = new OleDbConnection(connStr);
            conn.Open();
            OleDbCommand com = new OleDbCommand(strSql, conn);
            com.Parameters.Add("@GiaGoc", OleDbType.Double);
            com.Parameters.Add("@GiaBan", OleDbType.Double);
            com.Parameters.Add("@ID", OleDbType.Integer);

            com.Parameters["@GiaGoc"].Value = giaGoc;
            com.Parameters["@GiaBan"].Value = giaBan;
            com.Parameters["@ID"].Value = id;
            com.ExecuteNonQuery();
            conn.Close();
        }
    }
}
